

<?php
	function get_connection() {
		$con = mysql_pconnect('147.175.187.53', 'root', 'qwerty123', 65536);
		if (!$con) {
			echo('<h3 style="color: Red;">Cannot connect to MySQL server &nbsp;&nbsp; :\'(</h3>');
			die('Could not connect: ' . mysql_error());
		}
		mysql_query("SET NAMES 'utf8'");
		mysql_select_db("study_department", $con);
		return $con;
	}
	
	function get_connection_stagingarea() {
		$con = mysql_pconnect('147.175.187.53', 'root', 'qwerty123', 65536);
		if (!$con) {
			echo('<h3 style="color: Red;">Cannot connect to MySQL server &nbsp;&nbsp; :\'(</h3>');
			die('Could not connect: ' . mysql_error());
		}
		mysql_query("SET NAMES 'utf8'");
		mysql_select_db("stagingarea", $con);
		return $con;
	}
	
	function getConnectionHwSw() {
		$con = mysql_pconnect('147.175.187.53', 'root', 'qwerty123', 65536);
		if (!$con) {
			echo('<h3 style="color: Red;">Cannot connect to MySQL server &nbsp;&nbsp; :\'(</h3>');
			die('Could not connect: ' . mysql_error());
		}
		mysql_query("SET NAMES 'utf8'");
		mysql_select_db("hw_sw_evidence", $con);
		return $con;
	}
	
	// Vseobecne
	$connection = get_connection_stagingarea();
	$queryString2 = "TRUNCATE time" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
	
	$connection = get_connection_stagingarea();
	$queryString2 = "TRUNCATE faculty" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
    $queryString = "INSERT INTO faculty (`FacultyKey`,`Name`,`Address`) VALUES (1,'Fakulta informatiky a informaÄ�nÃ½ch technolÃ³giÃ­','IlkoviÄ�ova 3') " ;
	$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
				
	while($row = mysql_fetch_array($queryAnswer) ) {
		$text .= $row['ais_id'];
				
	}
	//mysql_close($connection);
	
	//*************************************************************************************
	echo "Naplnena tabulka: fieldofStudy";
	//naplnanie tabulky fieldofstudy
	$connection = get_connection();
		
    $queryString = "SELECT programme FROM `t_student` GROUP BY programme ORDER BY programme " ;
	$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
	$connection = get_connection_stagingarea();			
	$index=0;
	$queryString2 = "TRUNCATE fieldofstudy" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
	while($row = mysql_fetch_array($queryAnswer) ) {
		if ($row['programme']!=""){
			$index++;
    		$queryString2 = "INSERT INTO fieldofstudy (`FieldOfStudyKey`,`Name`) VALUES (".$index.",'".$row['programme']."') " ;
			$queryAnswer2 = mysql_query($queryString2,$connection) or die(mysql_error()); 
		}		
	
	}
	
	//**************************************************************************************
	echo ", Student";
	//naplnenie tabulky student
	$connection = get_connection();
		
    $queryString = "SELECT p.id, st.programme, p.ais_id, firstname.value AS firstname, surname.value AS surname,st.year FROM study_department.t_person p
					left join study_department.t_person_data firstname on firstname.person_id=p.id AND firstname.data_type_id=1 
					left join study_department.t_person_data surname on surname.person_id=p.id AND surname.data_type_id=2
					left join study_department.t_student st on st.person_id=p.id  
					WHERE person_type_id=1" ;
	$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
	
	$connection = get_connection_stagingarea();	
	
	$index=0;
	
	$queryString2 = "TRUNCATE student" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 


	
	while($row = mysql_fetch_array($queryAnswer) ) {
			$fieldOfStudyID=0;
			$qSFieldOfStudy = "SELECT * FROM fieldofstudy" ;
			$qAFieldOfStudy = mysql_query($qSFieldOfStudy,$connection)or die(mysql_error()); 
			while($rowFS = mysql_fetch_array($qAFieldOfStudy) ) {
				if ($rowFS[Name]==$row[programme]) $fieldOfStudyID=$rowFS[FieldOfStudyKey];
			}
			$index++;
			
			
			

			
    		$queryStringStudent = "INSERT INTO student (`StudentKey`,`AIS_ID`,`LibraryAccountKey`,`FieldOfStudyKey`,`StudyStatusKey`,`FirstName`,`LastName`,`Grade`) VALUES ( 	'".$index."','".$row['ais_id']."',0,".$fieldOfStudyID.",0,'".$row['firstname']."','".$row['surname']."','".$row['year']."') " ; //treba dorobit LibraryAccountKey StudyStatusKey
			
    		$queryAnswerStudent = mysql_query($queryStringStudent,$connection) or die(mysql_error()); 
			
			
	
	}
	//************************************************************
	echo ", Course";
	//naplnenie tabulky Course
	
	$connectionHwSw = get_connection();	
	$queryStringHwSw = "SELECT name_sk, guarantor_name FROM `study_department`.`t_subject`" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	$connection = get_connection_stagingarea();
	$queryString2 = "TRUNCATE course" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
	
	while($row = mysql_fetch_array($queryAnswerHwSw) ) {
			
			$queryString= "INSERT INTO course (`Name`,`GuarantorName`) VALUES ('".$row[name_sk]."','".$row[guarantor_name]."') " ;
    		$queryAnswer = mysql_query($queryString,$connection) or die(mysql_error()); 
    	
	}
	
	//**********************************************************************************	
	echo ", examType";
	//naplnenie tabulky examtype
	
	$connectionHwSw = get_connection();	
	$queryStringHwSw = "SELECT type FROM `study_department`.`t_exam` GROUP BY type" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	$connection = get_connection_stagingarea();
	$queryString2 = "TRUNCATE examtype" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
	
	while($row = mysql_fetch_array($queryAnswerHwSw) ) {
			
			$queryString= "INSERT INTO examtype (`Name`) VALUES ('".$row[type]."') " ;
    		$queryAnswer = mysql_query($queryString,$connection) or die(mysql_error()); 
    	
	}
	
	//**********************************************************************************
	/*echo "Exam\n";
	//naplnenie tabulky Time pri Exam

	$connectionHwSw = get_connection();	
	$queryStringHwSw = "SELECT date FROM `study_department`.`t_exam` GROUP BY date" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	$connection = get_connection_stagingarea();
		
	while($row = mysql_fetch_array($queryAnswerHwSw) ) {
			
			$queryString= "INSERT INTO time (`Date`) VALUES ('".$row[date]."') " ;
    		$queryAnswer = mysql_query($queryString,$connection) or die(mysql_error()); 
    	
	}	*/
	
	//**********************************************************************************
		mysql_close($connection);
	mysql_close($connectionHwSw);
	

